Cube View Performance

Cube view performance includes the following information:

• Database Sparsity

• Row and Column Suppression

• Suppressed Members

• Sparse Row Suppression

• Cube View Paging

Database Sparsity

Sparsity is the ratio of data record volumes in the cube compared to the dimensions modeled in the cube. We see sparsity when the data unit (combination of cube, entity, parent, consolidation, scenario, and time) has sparsely populated data intersections across the account-type dimensions (account, intercompany, flow, and user-defined). The absence of data records can affect reporting performance because it is difficult to render reports if data is sparse.

Avoid sparsity in your application design when possible. Use analytic blend, extensibility, or other design frameworks to ensure the Dimension Library is designed for your reporting structure and to minimize sparsity. However, even with an optimal design, sparsity can still occur if a large report pulls in a lot of dimensions. Suppression settings can help improve reporting performance if this is the case.

Row and Column Suppression

Use row and column suppression in larger reports to make them easier to read while still enabling the report builder to create a low maintenance report based on the metadata design.

To adjust the suppression settings:

1. On the Cube Views page, under Cube View Groups, select a cube view.

2. Select Rows and Columns to expand the slider.

3. Select the row or column and then click the Data tab. The following settings are available.

• Suppress Invalid Rows/Columns: Set to True to suppress any invalid cells.

• Suppress NoData Rows/Columns: Set to True to suppress any cells without data. When data entry is required, the recommended setting is False.

• Suppress Zero Rows/Columns: Set to True to suppress any cells containing zeros.

• Use Suppression Settings on Parent Rows/Columns: This property relates to the prior properties and controls whether the parent members in this member filter use the same settings.

• Zero Suppression Threshold: When Suppress Zero Rows/Columns is set to True, enter a value to suppress all numbers below it. All numbers below the specified number are recognized as zeros. For example, entering 499.99 results in every number lower than that value being recognized as zero and therefore suppressed.

• Allow Insert Suppressed Member (rows only): Use this setting to access a member currently suppressed for data entry purposes. You can only use this with cube views and form templates.

  • All: Enables visibility to all cube view row expansions

  • False: All row expansions remain suppressed

  • Nested: Enables visibility of the row expansions two through four

  • Innermost: Enables visibility of the row expansion that is at the bottom level

• User To Determine Row Suppression (columns only): Set to True to improve performance on large cube views by enabling the designer to better define how to apply row suppression.

• Allow Sparse Row Suppression (columns only): Provides performance improvements for cube views that use multiple nested row dimensions and works in conjunction with a General Settings property. When set to True, sparse row suppression is applied to the entire cube view. It can be turned on and off for specific columns.

Suppressed Members

This section includes instructions for how to manage suppressed members.

Apply Modify Suppression Property

With this setting, you can choose whether rows are suppressed from the data explorer grid. This feature is useful for a small cube view that will not generate a large number of additional rows if the suppression is turned off.

1. On the Cube Views page, under Cube View Groups, select a cube view.

2. Go to General Settings > Common.

3. Under Restrictions, set the Can Modify Suppression property to True.

4. Click Save.

Modify Suppression

To modify suppression, you must first apply the correct property. See Apply Modify Suppression Property.

1. Open a cube view in Data Explorer view.

2. Select the Row Suppression drop-down menu.

   

3. Choose the option to apply.

   • Use Default Row Suppression: Apply the cube view suppression settings.

   • Suppress Rows: Suppress any rows with zeros or no data regardless of the cube view settings.

   • Unsuppress Rows: Unsuppress all rows that were suppressed with zeros, no data, or invalid data regardless of the cube view settings.

Apply Property to Allow Insert Suppressed Members

You can insert suppressed members so that their data is entered in the cube view.

1. On the Cube Views page, under Cube View Groups, select a cube view.

2. Go to Rows and Columns > Data > Suppression and confirm that Allow Insert Suppressed Member is set to All.

   NOTE: The Allow Insert Suppressed Member option is only available for rows.

3. Click Save.

Insert Suppressed Members

To insert suppressed members, you must first apply the correct property. See Apply Property to Allow Insert Suppressed Members.

1. Open a cube view in Data Explorer view.

2. Right-click in the account or entity.

3. Click Insert Suppressed Member.

4. To the right of the account or entity field, click Select Member.

5. Select an item and click the arrow to add it to the Result List.

6. Click OK and then click OK to confirm.

7. Enter data as needed in the white cells.

8. Click Save and then click OK.

Sparse Row Suppression

Designs for analytic reports typically have multiple dimensions nested in rows. The combination of members generated from the nested expansions can easily result in billions of potential expanded rows, many of which may not have data. In these designs, standard row and column suppression (invalid, no data, or zero) in the cube view would still require each of those billion rows to be inspected individually for data. So, we recommend sparse row suppression be enabled to enhance the performance of a large cube view when, due to widespread database sparsity, the report is likely to return many records without data and take a long time to run. Sparse row suppression evaluates the data records of the cube view intersections and filters records with no data (not zeros) before rendering the cube view.

IMPORTANT: Sparse row suppression cannot be applied to dynamically calculated data through dynamically calculated members and cube view math. Avoid errors by correctly applying the settings on the dynamically calculated columns.

NOTE: When sparse row suppression is applied, OneStream assesses all row data before displaying the cube view. Cells containing dynamic calculations are populated on-the-fly. When OneStream assesses the rows of data, the rows containing dynamically calculated data are omitted from the cube view because the data is not saved in the database.

To enable sparse row suppression in cube views:

1. On the Cube Views page, under Cube View Groups, select a cube view.

2. Go to General Settings > Common.

3. Under Suppression, set Allow Sparse Row Suppression to True.

4. Select Rows and Columns to expand the slider. Select the row and then the Data tab.

5. Ensure the additional row suppression properties you will use are also set to True.

   NOTE: Any row assigned a suppression setting will be enabled for sparse row suppression. If no suppression is applied, sparse row suppression will not be applied.

6. In the Rows and Columns slider, select the column and then the Data tab.

7. Under Suppression, set Allow Sparse Row Suppression to True.

8. Ensure the additional column suppression properties you will use are also set to True.

   NOTE: If columns contain dynamically calculated data, set Use to Determine Row Suppression and Allow Sparse Row Suppression to True to avoid an error.

Cube View Paging

Cube view paging is only applied to the data explorer view of a cube view and is used to enhance the performance of cube views containing more than 10,000 unsuppressed rows. The cube view will attempt to return up to 2000 unsuppressed rows within a maximum processing time of 20 seconds. The purpose of paging is to protect the server from large cube views that could affect application performance.

Evaluations are performed on the potential size of the rows and the processing requirements to determine if paging is enabled and the number of rows returned.

• Evaluation 1 – Enable Paging: An evaluation is performed on the entire cube view to determine the total number of possible unsuppressed rows that will be generated. If the total number of potential unsuppressed rows is less than 10,000, no paging will be enabled.

• Evaluation 2 – Paging Enabled: If the total number of unsuppressed rows is greater than or equal to 10,000, paging is enabled.

• Evaluation 3 – Paging: Once paging begins, the cube view evaluates the rows attempting to return a minimum of 20 to a maximum of 2000 unsuppressed rows. In the case of nested dimensions on rows, the evaluation starts on the left most dimension expansion, as defined in the cube view. After a maximum processing time of 20 seconds, the first page of the cube view will be returned for display containing only the rows that completed processing during the time constraint. For this reason, cube view pages are not a fixed number of rows. The rows are ultimately determined by their time to process. This also relates directly to the percentage display, because each page is generated by processing time requirements, and the last page is not known while the cube view is running. Therefore, this percentage is not intended to be a precise measurement.

When a row is defined with nested dimensions, the paging evaluation is performed on the left most dimension. For each expansion of the left most dimension, the paging will not progress to the next sibling until all the records are returned by all the other dimension expansions to be completed.

To set the properties, on the Cube Views page, under Cube View Groups, select a cube view. Go to General Settings > Common. The following settings are available.

• Max Unsuppressed Rows Per Page: Determines how many rows are written before the cube view starts paging (default is -1). The maximum value is 100,000.

• Max Seconds To Process: Determines how many seconds the cube view processes before it starts paging (default is -1). The maximum value is 600 seconds.

See Cube View Paging for more information on how to cancel a long-running cube view through Task Activity.